<?php
defined ( 'SYSPATH' ) or die ( 'No direct access allowed.' );
/*
 * 采购收货明细报表
 * @author chamlinsun@qq.com
 */
class Controller_recieve_detail extends Controller_Admin{

    public function before(){
    	try {
    		  ACL::Required('recieve_detail_report');
    	} catch ( HTTP_Exception_403 $e ) {
				Message::error('sorry，你没有权限进这个目录');
    		$this->request->redirect ( URL::base (true) );
    	}
        parent::before();
    }
	
	public function Action_index(){
		$this->title = __("收货明细报表");
		$view = View::factory('from/detailsfrom');
		$this->response->body($view);		
	}
	
	public function Action_select(){
		$sql = " SELECT a.recieve_time, b.id, a.supplier, a.store,
            a.enterprise_dominant, b.sku, b.sku_name, b.sku_standard, b.storage_position,
			b.arrival_quantity, b.purchase_quantity, po.purchase_id, po.id AS poid,
            po.export_tax_rebate AS export_tax_rebate ";
		
		$sel_sql = $this->Select_sql($_GET);

		if($sel_sql){
			$sql .= $sel_sql;
			$receieve_lists = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
		}

        $pagination = Pagination::factory(array(
            'items_per_page' => 10,
            'total_items' => count($receieve_lists),
            'uri' => $this->request->uri(),
        ));
        if(isset($_GET['down'])){
            $this->Action_load($receieve_lists);die;
        }
        $current_page = isset($_GET['page']) ? $_GET['page'] : 1;
        $limit = "  LIMIT " . (($current_page - 1) * 10) . ",10";
        $receieve_lists = DB::query(Database::SELECT, $sql.$limit)->execute('newerp')->as_array();
        $skuArr = Position::array_columns($receieve_lists,'sku');
        $skuPositionArr = Position::getSkusPostion($skuArr);
        //如果没有储位就继续使用采购明细表的信息
        if($skuPositionArr){
            foreach($receieve_lists as $key => &$list){
                $list['storage_position'] = isset($skuPositionArr[$list['sku']])
                    ?$skuPositionArr[$list['sku']] : $list['storage_position'];
				$sql = "SELECT `export_tax_rebate` FROM `purchaseorders`
                    WHERE `id`=".$list['poid'];
				$export_tax_rebate = DB::query(Database::SELECT,$sql)->execute('newerp')->get('export_tax_rebate');
				$list[$key]['export_tax_rebate'] = $export_tax_rebate;
            }
            unset($list);
        }

        $this->title = __("收货明细报表");
        $view = View::factory('from/detailsfrom');
		$view->set('receieve_lists',$receieve_lists);
		$view->set('pagination',$pagination);
        $this->response->body($view);
	}
	
	//获取sum值
	public function Action_select_sum(){
		$re = array();
		//到货总量,未到货总量
		$sum_sql = " SELECT COUNT(b.id) AS ids ";
		$sum_sql .= " ,SUM(b.arrival_quantity) AS sum_arrival_quantity ";
		$sum_sql .= " ,SUM(b.purchase_quantity-b.arrival_quantity) AS sum_no_arrival ";
		$sql = $this->Select_sql($_POST);
		
 		if($sql){
			$sum_recieve = DB::query(Database::SELECT, $sum_sql.$sql)->execute('newerp')->current();
			$re['sum_ids'] = isset($sum_recieve['ids'])?$sum_recieve['ids']:0;
			$re['sum_arrival_quantity'] = isset($sum_recieve['sum_arrival_quantity'])?$sum_recieve['sum_arrival_quantity']:0;
			$re['sum_no_arrival'] = isset($sum_recieve['sum_no_arrival'])?$sum_recieve['sum_no_arrival']:0;
 		} 

 		echo json_encode($re);exit;
	}
	
	protected function Select_sql(array $condtion){
		$sql = "";
		$sql .= " FROM recieve_invoices AS a,recieve_details AS b,purchaseorders AS po ";
		$sql .= " WHERE a.id=b.recieve_invoice_id ";
		$sql .= " AND a.purchaseorder_id = po.id ";
		
		//采购员
		if(isset($condtion['purchaser_name']) and $condtion['purchaser_name']){
			$purchaserid = Erpuser::lookup_by_nick($condtion['purchaser_name']);
			//采购员id
			if($purchaserid){
				$sql .= " AND po.purchase_id = ".$purchaserid;
			}
			else{
				return NULL;
			}
		}
		if(isset($condtion['export_tax_rebate'])){
		    switch ($condtion['export_tax_rebate']){
		        case 0:
		        case 1:
		            $sql .= " AND a.export_tax_rebate = ".$condtion['export_tax_rebate'];
		            break;
		        default:
		            break;
		    }
		}
        if(isset($condtion['enterprise_dominant']) && $condtion['enterprise_dominant']>0){
            $sql .= " AND a.enterprise_dominant = ".$condtion['enterprise_dominant'];
        }
		
		if(isset($condtion['date_from']) and $condtion['date_from']){
			$sql .= " AND a.recieve_time >= '".$condtion['date_from']."'";
		}

		if(isset($condtion['date_to']) and $condtion['date_to']){
			$sql .= " AND a.recieve_time <= '".$condtion['date_to']." 23:59:59 '";
		}

/*         if(isset($condtion['batch_code']) and $condtion['batch_code']){
            $sql .= " AND b.batch_code = '".$condtion['batch_code']."'";
        } */
		
		if(isset($condtion['sku']) and $condtion['sku']){
			$sql .= " AND b.sku = '".$condtion['sku']."'";
		}
		
		//供应商
		if(isset($condtion['supplier_name']) and $condtion['supplier_name']){
            $name = $condtion['supplier_name'];
            $supp_id = Newerpclientapi::getSuppliers($name);
//            $supp_sql = " SELECT id FROM erp_provider WHERE vProviderName LIKE '%".$condtion['supplier_name']."%'";
//			$supp_id = DB::query(Database::SELECT, $supp_sql)->execute('readonlyerp')->as_array();
			if(empty($supp_id)){
				return NULL;
			}else {
				$supplier_ids = '';
				foreach ($supp_id as $supplier){
					$supplier_ids .= $supplier_ids?",".$supplier['id']:$supplier['id'];
				}
				$sql .= " AND a.supplier IN (".$supplier_ids.")";
			}
		}
		$sql .= " ORDER BY b.id DESC ";

		//收货时间，SKU，供应商，采购量，到货量
		if(isset($condtion['order_by']) and $condtion['order_by'] == 1 ){
			$sql .= " , a.recieve_time DESC";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 2){
			$sql .= " , b.sku ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 3){
			$sql .= " , a.supplier ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 4){
			$sql .= " , b.purchase_quantity ";
		}
		elseif (isset($condtion['order_by']) and $condtion['order_by'] == 5){
			$sql .= " , b.arrival_quantity ";
		}
		/*else{
			$sql .= " , a.recieve_time DESC";
		}*/

//		//一次多少条
//		if(isset($condtion['limit'])){
//			$sql .= " LIMIT ".$condtion['limit'];
//		}
//
//		//偏移
//		if(isset($condtion['offset'])){
//			$sql .= " OFFSET ".$condtion['offset'];
//		}
		return $sql;
	}
	
	public function Action_load($receieve_lists){
		set_time_limit(0);
		ini_set('memory_limit','512M');
		
//		$sql = " SELECT a.recieve_time, b.id, a.supplier, a.store,
//            a.enterprise_dominant, b.sku, b.sku_name, b.sku_standard, b.storage_position,
//			b.arrival_quantity, b.purchase_quantity, po.purchase_id, po.id AS poid,
//            po.export_tax_rebate AS export_tax_rebate ";
//		$sel_sql = $this->Select_sql($_GET);
//
//		if($sel_sql){
//			$receieve_lists = DB::query(Database::SELECT, $sql.$sel_sql)
//    			->execute('newerp')
//    			->as_array();
//		}
		if($receieve_lists){
            $skuArr = Position::array_columns($receieve_lists,'sku');
            $skuPositionArr = Position::getSkusPostion($skuArr);
            //如果没有储位就继续使用采购明细表的信息
            if($skuPositionArr){
                foreach($receieve_lists as $key=>$list){
                    $list['storage_position'] = isset($skuPositionArr[$list['sku']])?$skuPositionArr[$list['sku']] : '';
					$list['storage_position'] = isset($skuPositionArr[$list['sku']])?$skuPositionArr[$list['sku']] : '';
					$sql = "SELECT `export_tax_rebate`
                        FROM `purchaseorders`
                        WHERE `id`=".$list['poid'];
					$export_tax_rebate = DB::query(Database::SELECT,$sql)->execute('newerp')->get('export_tax_rebate');
					$list[$key]['export_tax_rebate'] = $export_tax_rebate;
                }
                unset($list);
            }

            require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
			
			$objPHPExcel = new PHPExcel();
			$objPHPExcel->getProperties()->setCreator("YKS")
    			->setLastModifiedBy("YKS")
    			->setTitle("Office 2003 XLSX Test Document")
    			->setSubject("Office 2003 XLSX Test Document")
    			->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
    			->setKeywords("office 2003 openxml php")
    			->setCategory("Test result file");
			$objActSheet = $objPHPExcel->setActiveSheetIndex(0);
			
			$objActSheet->setCellValue('A1', '序号');
			$objActSheet->setCellValue('B1', '收货日期');
			$objActSheet->setCellValue('C1', '收货明细单号');
			$objActSheet->setCellValue('D1', 'VMI预约单号');
			$objActSheet->setCellValue('E1', '供应商');
			$objActSheet->setCellValue('F1', '是否退税');
			$objActSheet->setCellValue('G1', 'SKU');
			$objActSheet->setCellValue('H1', '中文名称');
			$objActSheet->setCellValue('I1', '仓库');
			$objActSheet->setCellValue('J1', '主体');
			$objActSheet->setCellValue('K1', '规格');
			$objActSheet->setCellValue('L1', '储位');
			$objActSheet->setCellValue('M1', '计划员');
			$objActSheet->setCellValue('N1', '采购数量');
			$objActSheet->setCellValue('O1', '到货数量');
			$store_arr = Kohana::$config->load('vmi_warehouse')->as_array();
			$body_arr = Kohana::$config->load('enterprise_dominant')->as_array();
			foreach ($receieve_lists as $key => $receieve_list){
			    $export_tax_rebate = ($receieve_list['export_tax_rebate']==0)?'非出口退税':'出口退税';
		        $storeName = isset($store_arr[$receieve_list['store']]) ? $store_arr[$receieve_list['store']]:'';
                $body = isset($body_arr[$receieve_list['enterprise_dominant']]) 
                	? $body_arr[$receieve_list['enterprise_dominant']] : '';
				$objActSheet->setCellValue('A'.($key+2), $key + 1);
				$objActSheet->setCellValue('B'.($key+2),$receieve_list['recieve_time']);
				$objActSheet->setCellValue('C'.($key+2),$receieve_list['id'] );
				$objActSheet->setCellValue('D'.($key+2),$receieve_list['poid'] );
				$objActSheet->setCellValue('E'.($key+2),supplierskus::getNameByIdForVmi($receieve_list['supplier']));
				$objActSheet->setCellValue('F'.($key+2),$export_tax_rebate);
				$objActSheet->setCellValue('G'.($key+2),$receieve_list['sku']);
				$objActSheet->setCellValue('H'.($key+2),$receieve_list['sku_name']);
				$objActSheet->setCellValue('I'.($key+2),$storeName);
				$objActSheet->setCellValue('J'.($key+2),$body);
				$objActSheet->setCellValue('K'.($key+2),$receieve_list['sku_standard']);
				$objActSheet->setCellValue('L'.($key+2),$receieve_list['storage_position']);
				$objActSheet->setCellValue('M'.($key+2),Erpuser::nick_by_id($receieve_list['purchase_id']));
				$objActSheet->setCellValue('N'.($key+2),$receieve_list['purchase_quantity']);
				$objActSheet->setCellValue('O'.($key+2),$receieve_list['arrival_quantity']);
			}
			
			$objPHPExcel->getActiveSheet()->setTitle('收货明细报表');
			$objPHPExcel->setActiveSheetIndex(0);
			$day = date("m-d");
			$filename = $day.'收货明细报表.xls';
            ob_end_clean();
            header("Content-Type: application/vnd.ms-excel; charset=utf-8");
			header('Content-Disposition: attachment;filename='.$filename);
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			exit;
		}
	}  
}
